package cn.edu.cuc.yjsy.admin.controllers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.eclipse.core.runtime.IProgressMonitor;
import org.eclipse.core.runtime.IStatus;
import org.eclipse.core.runtime.Status;
import org.eclipse.core.runtime.SubProgressMonitor;
import org.eclipse.core.runtime.jobs.Job;
import org.eclipse.emf.common.util.EList;
import org.eclipse.emf.ecore.EAttribute;
import org.eclipse.emf.ecore.EStructuralFeature;
import org.eclipse.osgi.util.NLS;

import cn.edu.cuc.yjsy.model.LearnCategory;
import cn.edu.cuc.yjsy.model.LevelCategory;
import cn.edu.cuc.yjsy.model.MatriculandCategory;
import cn.edu.cuc.yjsy.model.ModelFactory;
import cn.edu.cuc.yjsy.model.ModelManager;
import cn.edu.cuc.yjsy.model.ModelPackage;
import cn.edu.cuc.yjsy.model.GenderCategory;
import cn.edu.cuc.yjsy.model.ResultCategory;
import cn.edu.cuc.yjsy.model.Student;
import cn.edu.cuc.yjsy.admin.Activator;
import cn.edu.cuc.yjsy.admin.connection.IConnection;
import cn.edu.cuc.yjsy.admin.connection.SourceConnection;
import cn.edu.cuc.yjsy.admin.connection.TargetConnection;
import cn.edu.cuc.yjsy.admin.util.MessageHelper;
import cn.edu.cuc.yjsy.admin.util.ILogger;
import cn.edu.cuc.yjsy.admin.util.LoggerAdvisor;
import cn.edu.cuc.yjsy.admin.util.Op;

/**
 * 
 * @author zhumx
 *
 */
public class StudentController{

	private ILogger logger = new LoggerAdvisor(StudentController.class);
	
	class ImportJob extends Job{

		boolean bOverWrite;
		String filePath;
		String tableName;
		int[] uniqueFeature = {ModelPackage.STUDENT__IDNUMBER, 
				ModelPackage.STUDENT__NAME, ModelPackage.STUDENT__EXAMINENUMBER}; 
		
		public ImportJob(String name, String file, String tableName, boolean overWrite) {
			super(name);
			this.filePath = file;
			this.tableName = tableName;
			this.bOverWrite = overWrite;
		}

		/**
		 * @Override
		 */
		protected IStatus run(IProgressMonitor monitor) {
			IStatus status = Status.OK_STATUS;
			List rt = null;
			try{
				monitor.beginTask(MessageHelper.IMPORT_JOB_NAME, 100);
	
//				monitor.setTaskName(NLS.bind(MessageHelper.IMPORT_JOB_LOAD_DATA,
//						filePath));
				monitor.subTask(NLS.bind(MessageHelper.IMPORT_JOB_LOAD_DATA,
						filePath));
				IConnection connection = new SourceConnection(filePath, tableName);
				rt = query(connection, new HashMap());
				monitor.worked(40);
				
				Map options = new HashMap();
				if(rt != null){
					List rt1 = null;
					SubProgressMonitor subMonitor = new SubProgressMonitor(monitor, 60);
					try{
						subMonitor.beginTask(MessageHelper.IMPORT_JOB_WRITE_DATA, rt.size());
						StringBuilder errorName = new StringBuilder();
						for(Iterator iter = rt.iterator(); iter.hasNext();){
							Student s = (Student)iter.next();
							options.clear();
							for(int i = 0; i < uniqueFeature.length; i++){
								EStructuralFeature feature = ModelPackage.eINSTANCE.getStudent().
									getEStructuralFeature(uniqueFeature[i]);
								if(s.eGet(feature) != null){
									String value = s.eGet(feature).toString();
									options.put(Integer.valueOf(uniqueFeature[i]), 
											Op.toLocaleString(value));
								}
							}			
							String name = s.getName();							
							try{
								IConnection conn = TargetConnection.getInstance();
								rt1 = query(conn, options);
								if(rt1 != null && rt1.size() > 0){															
									if(bOverWrite){
										subMonitor.setTaskName(NLS.bind(
												MessageHelper.IMPORT_JOB_OVERWRITE_DATA, name));
										update(conn, s, options);
									}else{
										subMonitor.setTaskName(NLS.bind(
												MessageHelper.IMPORT_JOB_IGNORE_EXIST_DATA, name));
									}
								}else{
									subMonitor.setTaskName(NLS.bind(
											MessageHelper.IMPORT_JOB_INSERT_DATA, name));
									insert(conn, s);
								}
								subMonitor.worked(1);
							} catch (SQLException e) {
								logger.error(NLS.bind(MessageHelper.IMPORT_JOB_ERROR_WRITE_DATA, name));
								errorName.append(name + " ");
								status = new Status(Status.ERROR, Activator.PLUGIN_ID, IStatus.ERROR,
										NLS.bind(MessageHelper.IMPORT_JOB_ERROR_WRITE_DATA, errorName), e);
								e.printStackTrace();
								subMonitor.worked(1);
							}
						}	
					} finally{
						subMonitor.done();
					}					
				}
			} catch (SQLException e) {
				this.cancel();
				logger.error(NLS.bind(MessageHelper.IMPORT_JOB_ERROR_READ_DATA, filePath));
				status = new Status(Status.ERROR, Activator.PLUGIN_ID, IStatus.ERROR, 
						NLS.bind(MessageHelper.IMPORT_JOB_ERROR_READ_DATA, filePath), e);
				e.printStackTrace();
			} catch(Exception e){
				e.printStackTrace();
			}finally{
				monitor.done();
			}
			return status;
		}
		
	}
	
	public StudentController(){
	}
	
	public void importData(String file, String tableName, boolean overWrite){
		Job importJob = new ImportJob(MessageHelper.IMPORT_JOB_NAME, file, 
				tableName, overWrite);
		importJob.setUser(true);
		importJob.schedule();
	}
	
	public void update(IConnection connection, Student s, Map options) throws SQLException{
		Map updateItems = new HashMap();
		EList features = ModelPackage.eINSTANCE.getStudent().getEAllStructuralFeatures();
		for(Iterator iter = features.iterator(); iter.hasNext();){
			EStructuralFeature feature = (EStructuralFeature)iter.next();
			
			String value = s.eGet(feature) == null ? "" : s.eGet(feature).toString();
			value = Op.toLocaleString(value);
			updateItems.put(Integer.valueOf(feature.getFeatureID()), value);
		}
		
		update(connection, updateItems, options);
	}
	
	public void update(IConnection connection, Map updateItems, Map conditions) throws SQLException{
		StringBuilder sql = new StringBuilder();
		sql.append("Update " + connection.getTableName() + " set ");
		boolean start = true;
		for(Iterator iter = updateItems.keySet().iterator(); iter.hasNext();){
			Integer feature = (Integer)iter.next();
			
			String value = (String)updateItems.get(feature);
			String fieldName = connection.getFieldName(feature.intValue());
			if(fieldName == null)
				continue;
			String newValue = fieldName + "='"
					+ value + "'"; 
			if(start){
				start = false;
				sql.append(newValue);
			}
			else{
				sql.append(",");
				sql.append(newValue);
			}
		}
		
		String condition = "";
		if(conditions.size() > 0){
			condition = "";
			Iterator iterator = conditions.keySet().iterator();
			start = true;
			while(iterator.hasNext()){
				Integer keyword = (Integer)iterator.next();
				if(start){
					condition += " where ";
					start = false;
				}
				else
					condition += " and ";
				condition += connection.getFieldName(keyword.intValue()) + " like '%" + (String)conditions.get(keyword) + "%'";
				
			}
		}
		
		sql.append(condition);
		
		try{
			connection.executeStatement(sql.toString());
		}finally{
			connection.dispose();
		}
		
	}
	
	public void insert(IConnection connection, Student s) throws SQLException{
		String sql = "Insert into " + connection.getTableName();
		String column = null;
		String values = null;
		boolean start = true;
		EList features = ModelPackage.eINSTANCE.getStudent().getEAllStructuralFeatures();
		for(Iterator iter = features.iterator(); iter.hasNext();){
			EStructuralFeature feature = (EStructuralFeature)iter.next();
			
			String value = s.eGet(feature) == null ? "" : s.eGet(feature).toString();
			value = Op.toLocaleString(value);
			String fieldName = connection.getFieldName(feature.getFeatureID());
			if(fieldName == null)
				continue;
			if(start){
				column = "(" + fieldName;
				values = "values('" + value + "'";
				start = false;
			}
			else{
				column += ", " + fieldName;
				values += ", '" + value + "'";
			}
		}
		column += ")";
		values += ")";
		
		sql += column + " " + values;
		try{
			connection.executeStatement(sql);
		}finally{
			connection.dispose();
		}
	}
	
	public List query(IConnection connection, Map conditions) throws SQLException{
		List rt = null;
		
		String condition = "";
		if(conditions.size() > 0){
			condition = "";
			Iterator iterator = conditions.keySet().iterator();
			boolean start = true;
			while(iterator.hasNext()){
				Integer keyword = (Integer)iterator.next();
				if(start){
					condition += " where ";
					start = false;
				}
				else
					condition += " and ";
				condition += connection.getFieldName(keyword.intValue()) + " like '%" + (String)conditions.get(keyword) + "%'";
				
			}
		}
		
		ResultSet rs = null;
		try {
			rs = connection.executeQuery("select * from " + Op.toLocaleString(connection.getTableName()) 
					+ condition);
			if (rs != null) {
				rt = fillStudent(connection, rs);
			}
		} finally{
			if(rs != null){
				rs.close();
				rs = null;
			}
			connection.dispose();
		}
		return rt;
	}
	
	List fillStudent(IConnection conn, ResultSet rs) throws SQLException{
		List list = new ArrayList();
		while (rs.next()) {
			ModelFactory factory = ModelManager.getFactory();
			Student s = factory.createStudent();
			
			EList attributes = factory.getModelPackage().getStudent().getEAllAttributes();
			for(Iterator iter = attributes.iterator(); iter.hasNext();){
				EAttribute attribute = (EAttribute)iter.next();
				if(attribute.isChangeable()){
					String typeName = attribute.getEType().getName();
					String column = conn.getFieldName(attribute.getFeatureID());
					if(column == null)
						continue;
					String value = rs.getString(column);
					if(value != null) {
						value = Op.toGBKString(value);
					}
					if(typeName.equals("EString"))
							s.eSet(attribute, value);
					else if(typeName.equals("GenderCategory"))
						s.eSet(attribute, GenderCategory.get(value));
					else if(typeName.equals("LearnCategory"))
						s.eSet(attribute, LearnCategory.get(value));
					else if(typeName.equals("LevelCategory"))
						s.eSet(attribute, LevelCategory.get(value));
					else if(typeName.equals("MatriculandCategory"))
						s.eSet(attribute, MatriculandCategory.get(value));
					else if(typeName.equals("ResultCategory"))
						s.eSet(attribute, ResultCategory.get(value));
					else if(typeName.equals("EDouble"))
						s.eSet(attribute, new Double(value));
//					else if(typeName.equals("EDate"))
//						s.eSet(typeName, new java.sql.Date(value));
				}
			}
			list.add(s);
		}
		
		return list;
	}
}
